Service Class

The Service class implements the main functionality required to perform most Create, Read, Update and Delete (CRUD) actions for a given database table.

There can be a unique Service class for each table, but if not defined, the Service class will be the one defined in exchange.pikaview.model_base. To learn more about definitng a unique Service class for each table, refer to the documentation on Extending the Base Classes.

Class Properties

Name	Type	Description
tableName	string	The name of the table to connect to.
schema	string	The name of the database to connect to.
RW_DB_CONNECTION	string	(Optional) A read/write capable database connection name which is registered on the Gateway. This can be the same as RO_DB_CONNECTION as long as that connection has read/write access, these are split to provide flexibility. If not provided, this will default to the value set in exchange.pikaview.settings.RW_DB_CONNECTION.
RO_DB_CONNECTION	string	(Optional) A read-only database connection name which is registered on the Gateway. This can be the same as RW_DB_CONNECTION if separating read-only traffic is not important. If not provided, this will default to the value set in exchange.pikaview.settings.RO_DB_CONNECTION.

Methods

querySingleById

Description

Creates and executes a SELECT query against the specific table and database specified in the service's tableName and schema for a single record indicated by the id parameter. For more information, refer to the Service properties.

Will return all fields available for the given table's metadata as well as any fields included due to a join.

Syntax

querySingleById(id, configID = None, fieldList = ['*'], joinsList = None, filterDict = None, orderDictList = None, limit = None, offset = None, toJson = False, toStr = False)

• Parameters
  • int id - The primary key of the record to be retrieved.
  • int configID - (Optional) The configuration ID to be used when retrieving metadata, which will limit the fields to what is selectable. Defaults to None, which will use the base configuration.
  • Join[] joinsList - a list of joins to perform, including any additional columns to be included in the select (join columns only availabed when toJson is True) OPTIONAL - defaults to None if not specified.
  • bool toJson - (Optional) A flag that controls the return type of the function, True will convert to json, otherwise will use Model class. Defaults to False.
  • bool toStr - (Optional) A flag that controls whether to output the built query string rather than executing it, which can be useful for debugging. Defaults to False.
• Returns
  • python dictionary ({}) OR Model OR string - Either the results of the query, which can either be a dictionary or Model form depending on the value of toJson, or a string containing the query string if toStr is True.

Examples

Get a service class for a given tableName and schema:

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

Get a Model instance for a given id:

model = service.querySingleById(id = 1)

Get a python dictionary for a given id:

jsonDictRecord = service.querySingleById(
    id = 1,
    toJson = True
)

Output the query string only:

queryStr = service.querySingleById(
    id = 1,
    toStr = True
)

queryMultiple

Description

Creates and executes a SELECT query against the specific table and database specified in the service's tableName and schema. For more information, refer to the Service properties.

Syntax

queryMultiple(configID = None, fieldList = ['*'], joinsList = None, filterDict = None, orderDictList = None, limit = None, offset = None, toJson = False, toStr = False)

• Parameters

  • int configID - (Optional) The configuration ID to be used when retrieving metadata, which will limit the fields to what is selectable. Defaults to None, which will use the base configuration.
  • Field[] | ['*'] fieldList - (Optional) Which fields to select from the base table, which doesn't include columns from Joins. Defaults to ['*'], which returns all columns if not specified.
  • Join[] joinsList - (Optional) A list of joins to perform, including any additional columns to be included in the select. Join columns are only available when toJson is True. Defaults to None.
  • Filter filterDict - (Optional) The conditional clauses to be built into the WHERE statement. Defaults to None.
  • OrderBy[] orderDictList - (Optional)The columns to order the results and the corresponding directions. Defaults to None.
  • int limit - (Optional) The limit of rows to return. Defaults to None, which means no limit will be applied.
  • int offset - (Optional) The offset of rows to retrieve, used in conjunction with limit parameter for pagination. Only applies if limit also specified. Defaults to None, which means no offset will be applied.
  • bool toJson - (Optional) A flag that controls the return type of the function, True will convert to json, otherwise will use Model class. Defaults to False.
  • bool toStr - (Optional) A flag that controls whether to output the built query string rather than executing it, which can be useful for debugging. Defaults to False.

• Returns

  • list of python dictionaries ([{}]) OR list of Model OR string - Either the results of the query, in dictionary or Model form depending on the value of toJson, or a string containing the query string if toStr is True.

Examples

Get a service class for a given tableName and schema:

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

Get all columns for all records:

allRecords = service.queryMultiple()

Selecting rows that match a certain filter criteria:

filteredRecords1 = service.queryMultiple(
    filterDict = {
        'logicalOperator': 'AND',
        'subItems': [
            {
                'field': {
                    'columnName' : 'name',
                    'tableName': tableName
                },
                'comparisonOperator': 'notnull'
            },
            {
                'field': {
                    'columnName': 'date',
                    'tableName': tableName
                },
                'comparisonOperator': 'between',
                'comparisonValue': system.date.addMonths(system.date.now(), -1),
                'comparisonValue2': system.date.now()
            }
        ]
    }
)

Selecting rows with an order by clause:

orderedRecords = service.queryMultiple(
    orderDictList = [
        {
            'field': {
                'columnName': 'date',
                'tableName': tableName
            },
            'direction': 'DESC'
        },
        {
            'field': {
                'columnName': 'name',
                'tableName': tableName
            },
            'direction': 'ASC'
        }
    ]
)

Selecting only certain columns from a table:

certainColumnRecords = service.queryMultiple(
    fieldList = [
        {
            'columnName': 'ID',
            'tableName': tableName
        },
        {
            'columnName': 'name',
            'tableName': tableName
        },
        {
            'columnName': 'date',
            'tableName': tableName
        }
    ]
)

Selecting a pseudo-column formed by a function call with an alias:

# select a single column which is a concatenation of the 'name' and 'date' columns with ' - ' in between
# EX: 'john - 01/01/2021'
pseudoColumnRecords = service.queryMultiple(
    fieldList = [
        {
            'function': 'Concat',
            'params': [
                {
                    'columnName': 'name',
                    'tableName': tableName
                },
                {
                    'stringValue': ' - '
                },
                {
                    'columnName': 'date',
                    'tableName': tableName
                }
            ],
            'alias': 'NameDateField'
        }
    ]
)

Doing a simple join to another table, and selecting columns from that table:

joinedRecords = service.queryMultiple(
    fieldList = [
        {
            'columnName': 'ID',
            'tableName': tableName
        },
        {
            'columnName': 'name',
            'tableName': tableName
        },
        {
            'columnName': 'date',
            'tableName': tableName
        }
    ],
    joinsList = [
        {
            'order': 1,
            'joinType': 'INNER',
            'joinTableName': tableName2,
            'joinTableSchema': schema2,
            'joinAlias': 'join1',
            'onCondition': {
                'comparisonOperator': '=',
                'field': {
                    'columnName': 'ID',
                    'tableName': tableName
                },
                'comparisonValue': {
                    'columnName': 'recordID',
                    'tableName': tableName2
                }
            },
            'selectFields': [
                {
                    'columnName': 'test1'
                },
                {
                    'columnName': 'test2'
                }
            ]
        }
    ]
)

Output the Query String only:

queryStr = service.queryMultiple(
    fieldList = [
        {
            'columnName': 'ID',
            'tableName': tableName
        },
        {
            'columnName': 'name',
            'tableName': tableName
        },
        {
            'columnName': 'date',
            'tableName': tableName
        }
    ],
    toStr = True
)

insertRecord

Description

Creates and executes an INSERT query to create a new record in the specific table and database specified in the service's tableName and schema. Refer to the Service Properties for more information.

Syntax

insertRecord(valueDict, txID)

• Parameters
  • python dictionary valueDict - A dictionary of key/value pairs representing the values to be inserted.
  • string txID - The transaction ID to be used with the database query, created via system.db.beginTransaction.
• Returns
  • int - The id of the newly created record, or -1 in case of uncaught exception.

Examples

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

newRecord = service.insertRecord(
    valueDict = {
        'name': 'newRecordName',
        'date': '2023-03-04',
        'magicNumber': 10
    },
    txID = None
)

updateRecord

Description

Creates and executes an UPDATE query to modify a record in the specific table and database specified in the service's tableName and schema. Refer to the Service Properties for more information.

Syntax

updateRecord(id, valueDict, txID)

• Parameters
  • int id - The id of an existing record to be updated
  • python dictionary valueDict - A dictionary of key/value pairs representing the values to be inserted.
  • string txID - The transaction ID to be used with the database query, created via system.db.beginTransaction.
• Returns
  • int - The number of rows updated or -1 in case of uncaught exception.

Examples

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

rowsUpdated = service.updateRecord(
    id = 1,
    valueDict = {
        'name': 'updatedRecordName',
        'magicNumber': 11
    },
    txID = None
)

deleteRecord

Description

Creates and executes a DELETE query to modify a record in the specific table and database specified in the service's tableName and schema. Refer to the Service Properties for more information.

Syntax

deleteRecord(id, txID)

• Parameters
  • int id - The id of an existing record to be updated.
  • string txID - The transaction ID to be used with the database query, created via system.db.beginTransaction.
• Returns
  • int - The number of rows delete or -1 in case of uncaught exception.

Examples

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

rowsUpdated = service.deleteRecord(
    id = 1,
    txID = None
)

retrieveModelMetadata

Description

Retrieves all metadata for the service's tableName and schema. Refer to the Service Properties for more information.

For more information on metadata records, refer to the Metadata Configuration documentation.

Syntax

retrieveModelMetadata(configID = None, toStr = False)

• Parameters
  • int configID - (Optional) The configuration ID to be used when retrieving metadata, which will determine which specific configuration will be retrieved. Defaults to None, which will use the default configuration.
  • bool toStr - (Optional) A flag that controls whether to output the built query string rather than executing it, which is useful for debugging. Defaults to False.
• Returns
  • list of python dictionaries - The metadata rows retrieved from the DB.

Examples

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

metadata = service.retrieveModelMetadata()

getNewInstance

Description

Creates a new instance of the Model class or a python dictionary without any previous data, besides defaults configured in the service's metadata.

Syntax

getNewInstance(configID = None, toJson = False)

• Parameters
  • int configID - (Optional) The configuration ID to be used when retrieving metadata, which will limit the fields to what is selectable. Defaults to None, which will use the base configuration.
  • bool toJson - (Optional) A flag that controls the return type of the function. True will convert to json, otherwise it will use the Model class.Defaults to False.
• Returns
  • a python dictionary OR a Model instance - Either a python dictionary or Model instance which represents the new blank record, depending on the value of toJson.

Examples

Get a Model instance:

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

newRecord = service.getNewInstance()

Get a default python dictionary:

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

newRecord = service.getNewInstance(toJson = True)

buildFromJson

Description

Creates an instance of the Model class from the given python dictionary, useful to convert from a json format to a Model format.

Syntax

buildFromJson(valueDict)

• Parameters
  • python dictionary valueDict - A dictionary of key/value pairs which represent various attributes on the object.
• Returns
  • A Model instance which contains the values of the original python dictionary.

Examples

tableName = 'myTable'
schema = 'myDB'
service = exchange.pikaview.service_factory.GetModelServiceClass(tableName, schema)

record = service.buildFromJson(
    valueDict = {
        'name': 'recordName1',
        'date': '2023-03-05',
        'magicNumber': 123
    }
)